﻿using System;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

/// <summary>
/// Summary description for DataConnection use for connection database
/// </summary>
class DataConnection
{
    public SqlConnection connection;
    public SqlCommand command;
    public bool flagConnection;
    public string errorMessage;
    #region Contractor
    public DataConnection()
    {
        connection = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
        if (command == null)
        {
            command = connection.CreateCommand();
        }
    }

    public DataConnection(string ConnectionString)
    {
        connection = new SqlConnection(ConnectionString);
        command = connection.CreateCommand();
    }
    #endregion
    #region Procedure Connection
    public bool OpenConnection()
    {
        if (connection.State != System.Data.ConnectionState.Open)
        {
            connection.Open();
            flagConnection = true;
        }
        else flagConnection = false;
        return flagConnection;
    }

    public void Close()
    {
        command.Connection.Close();
        flagConnection = false;
    }

    public void DisposeAll()
    {
        if (connection.State == System.Data.ConnectionState.Open)
        {
            command.Dispose();
            connection.Close();
            connection.Dispose();
        }
    }
    #endregion
    #region Procedure Command
    public void SetCommand(string commandText, CommandType commandType)
    {
        if (command != null)
        {
            command.CommandType = commandType;
            command.CommandText = commandText;
        }
        else return;
    }
    #endregion
    #region Procedure Parameter
    public void AddParameter(string parameterName, object value)
    {
        SqlParameter parameter = new SqlParameter(parameterName, value);
        command.Parameters.Add(parameter);
    }
    public void AddParameter(string[] parameterName, object[] parameterValue)
    {
        for (int i = 0; i < parameterName.Length; i++)
        {
            SqlParameter parameter = new SqlParameter(parameterName[i], parameterValue[i]);
            command.Parameters.Add(parameter);
        }
    }
    public void AddParameter(string parameterName, object value, DbType type, ParameterDirection direction)
    {
        SqlParameter parameter = new SqlParameter(parameterName, value);
        parameter.Direction = direction;
        command.Parameters.Add(parameter);
    }
    #endregion
    #region Execute DataReader
    public SqlDataReader ExecuteDataReader()
    {
        SqlDataReader dataReader = null;
        try
        {
            dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (System.Exception ex)
        {
            errorMessage = ex.Message;
        }
        finally
        {
            command.Dispose();
        }
        return dataReader;
    }
    public SqlDataReader ExecuteDataReader(string commandText, CommandType type)
    {
        SqlDataReader dataReader = null;
        try
        {
            SetCommand(commandText, type);
            this.OpenConnection();
            dataReader = command.ExecuteReader();
        }
        catch (System.Exception ex)
        {
            errorMessage = ex.Message;
        }
        return dataReader;
    }
    public SqlDataReader ExecuteDataReader(string commandText, CommandType type, SqlParameterCollection parameters)
    {
        SqlDataReader dataReader = null;
        try
        {
            SetCommand(commandText, type);
            foreach (SqlParameter para in parameters)
            {
                command.Parameters.Add(para);
            }
            this.OpenConnection();
            dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (System.Exception ex)
        {
            errorMessage = ex.Message;
        }
        return dataReader;
    }
    #endregion
    #region ExecuteNonQuery
    public int ExecuteNonQuery()
    {
        int i = 0;
        try
        {
            this.OpenConnection();
            i = command.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            errorMessage = ex.Message;
        }
        return i;
    }
    public int ExecuteNonQuery(string commandText, CommandType commandType)
    {
        int i = 0;
        try
        {
            SetCommand(commandText, commandType);
            this.OpenConnection();
            i = command.ExecuteNonQuery();
            this.Close();
        }
        catch (System.Exception ex)
        {
            errorMessage = ex.Message;
        }
        return i;
    }
    public int ExecuteNonQuery(string commandText, CommandType type, SqlParameterCollection parameters)
    {
        int i = 0;
        try
        {
            this.OpenConnection();
            SetCommand(commandText, type);
            command.Parameters.Clear();
            foreach (SqlParameter para in parameters)
            {
                command.Parameters.Add(para);
            }
            i = command.ExecuteNonQuery();
        }
        catch (System.Exception ex)
        {
            errorMessage = ex.Message;
        }
        finally
        {
            command.Dispose();
        }
        return i;
    }
    #endregion
    #region ExecuteScalar
    public object ExecuteScalar()
    {
        object row = null;
        try
        {
            this.OpenConnection();
            row = command.ExecuteScalar();
        }
        catch (Exception ex)
        {
            errorMessage = ex.Message;
        }
        finally
        {
            command.Dispose();
        }
        return row;
    }

    public object ExecuteScalar(string commandText, CommandType type)
    {
        object row = null;
        try
        {
            this.OpenConnection();
            this.SetCommand(commandText, type);
            row = command.ExecuteScalar();
            this.Close();
        }
        catch (Exception ex)
        {
            errorMessage = ex.Message;
        }
        finally
        {
            command.Dispose();
        }
        return row;
    }

    public object ExecuteScalar(string commandText, CommandType type, SqlParameterCollection parameters)
    {
        object row = null;
        try
        {
            this.OpenConnection();
            SetCommand(commandText, type);
            command.Parameters.Clear();
            foreach (SqlParameter para in parameters)
            {
                command.Parameters.Add(para);
            }
            row = command.ExecuteScalar();
        }
        catch (Exception ex)
        {
            errorMessage = ex.Message;
        }
        finally
        {
            command.Dispose();
        }
        return row;
    }

    #endregion
    #region Use DataAdapter
    public DataSet ExecuteDatSet()
    {
        SqlDataAdapter iDDA = null;
        DataSet ds = new DataSet();
        try
        {
            iDDA = new SqlDataAdapter(command);
            iDDA.Fill(ds);
        }
        catch (Exception ex)
        {
            errorMessage = ex.Message;
        }
        finally
        {
            command.Dispose();
        }
        return ds;
    }

    //Điền dữ liệu vào DataSet
    public int FillDataSet(DataSet dataSet)
    {
        int result = 0;
        try
        {
            this.OpenConnection();
            SqlDataAdapter iDA = new SqlDataAdapter(command);
            result = iDA.Fill(dataSet);
        }
        catch (Exception ex)
        {
            errorMessage = ex.Message;
        }
        finally
        {
            command.Dispose();
        }
        return result;
    }

    //Thực thi câu lệnh bằng đối tượng DataSet với câu lệnh SQL và kiểu câu lệnh.
    public DataSet ExecuteDatSet(string commandText, CommandType type)
    {
        DataSet ds = new DataSet();
        SqlDataAdapter iDA = null;
        try
        {
            SetCommand(commandText, type);
            iDA = new SqlDataAdapter(command);
            iDA.Fill(ds);
        }
        catch (Exception ex)
        {
            errorMessage = ex.Message;
        }
        finally
        {
            command.Dispose();
        }
        return ds;
    }

    //Thực thi câu lệnh bằng đối tượng DataSet với câu lệnh SQL, kiểu câu lệnh và tham số
    public DataSet ExecuteDatSet(string commandText, CommandType type, SqlParameterCollection parameters)
    {
        DataSet ds = new DataSet();
        SqlDataAdapter iDDA = null;
        try
        {
            SetCommand(commandText, type);
            command.Parameters.Clear();
            foreach (SqlParameter para in parameters)
            {
                command.Parameters.Add(para);
            }
            iDDA.Fill(ds);
        }
        catch (Exception ex)
        {
            errorMessage = ex.Message;
        }
        finally
        {
            command.Dispose();
        }
        return ds;
    }
    //lấy dữ liệu retun ra table
    public DataTable ExecuteDataTableAll(string commandText, CommandType type)
    {
        DataTable dataTable = new DataTable();
        try
        {
            SetCommand(commandText, type);
            SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
            dataAdapter.Fill(dataTable);
        }
        catch (System.Exception ex)
        {
            errorMessage = ex.Message;
        }
        finally
        {
            command.Dispose();
        }
        return dataTable;
    }
    public DataTable ExecuteDataTableLimit(string commandText, CommandType type, int startRecord, int numberRecord)
    {
        DataTable dataTable = new DataTable();
        try
        {
            SetCommand(commandText, type);
            SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
            dataAdapter.Fill(startRecord, numberRecord, dataTable);
        }
        catch (System.Exception ex)
        {
            errorMessage = ex.Message;
        }
        finally
        {
            command.Dispose();
        }
        return dataTable;
    }
    #endregion
    #region Other
    public int GetValidReturnOfProcedure(string stringProcedure, ParameterDirection direction)
    {
        int i = 0;
        try
        {
            this.OpenConnection();
            SqlParameter parameter = new SqlParameter("@resultOutput", SqlDbType.Int);
            parameter.Direction = direction;
            command.Parameters.Add(parameter);
            SetCommand(stringProcedure, CommandType.StoredProcedure);
            command.ExecuteNonQuery();
            i = int.Parse(parameter.Value.ToString());
            this.Close();
        }
        catch (System.Exception ex)
        {
            throw new Exception(ex.Message);
        }
        return i;
    }
    public object[] GetObject(string parameterName, object parameterValue)
    {
        object[] objItem = null;
        try
        {
            command.Parameters.Clear();
            AddParameter(parameterName, parameterValue);
            SqlDataReader reader = ExecuteDataReader("udsGetAutoId", System.Data.CommandType.StoredProcedure);
            int i = reader.FieldCount;
            if (reader.Read())
            {
                objItem = new object[i];
                reader.GetValues(objItem);
            }
            this.Close();
            reader.Dispose();
        }
        catch (System.Exception ex)
        {
            errorMessage = ex.Message;
        }
        return objItem;
    }

    public void BeginTransaction()
    {
        if (this.InTransaction)
        {
            return;
        }
        if (command.Connection.State != ConnectionState.Open)
        {
            command.Connection.Open();
        }
        SqlTransaction transaction = command.Connection.BeginTransaction();
        command.Transaction = transaction;
        InTransaction = true;
    }

    //Chuyển tác có tham số.
    public void BeginTransaction(IsolationLevel lv)
    {
        SqlTransaction transaction = command.Connection.BeginTransaction(lv);
        command.Transaction = transaction;
        InTransaction = true;
    }
    //Thực hiện Chuyển tác
    public void Comit()
    {
        if (InTransaction)
        {
            command.Transaction.Commit();
        }
        InTransaction = false;
    }

    //Thực thi chuyển tác không thành công trở về ban đầu.
    public void RollBack()
    {
        if (InTransaction)
        {
            try
            {
                command.Transaction.Rollback();
            }
            catch (InvalidOperationException ex)
            {
                if (HandleErrors)
                {
                    StringError.Append("Error : RollBack ");
                    StringError.Append(ex.Message);
                    StringError.Append("\n");
                };
                this.Close();
            }
            finally
            {
                InTransaction = false;
            }
        }
    }
    //Lỗi trả về
    public string GetError()
    {
        return strErr.ToString();
    }
    //Dọn dẹp lỗi và trả về lỗi.
    public string ClearError()
    {
        string str = strErr.ToString();
        strErr = new StringBuilder();
        return str;
    }

    private bool handleErrors;
    protected bool HandleErrors
    {
        get { return handleErrors; }
        set { handleErrors = value; }
    }
    private StringBuilder strErr = new StringBuilder();
    protected StringBuilder StringError
    {
        get { return strErr; }
        set { strErr = value; }
    }
    //Kiểm tra chuyển tác.
    private bool inTran = false;
    protected bool InTransaction
    {
        set { inTran = value; }
        get { return inTran; }
    }
    #endregion
}